﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Threading;
using System.IO;
using System.IO.Ports;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using TCA_User.Common;

namespace TCA_User
{
    public partial class Bill_List
    {
        #region table contend
        int First_Row;
        int Bill_col;
        int Date_col;
        int Car_col;
        int Route_col;
        int Fee_col;
        int OnFee_Col;
        int OffFee_Col;
        int Other_Fee_col;
        int CardNum_col;

        //const string VS_First_row_str = "Stt";
        //const string VS_First_row_str_2 = "STT";
        //const string VS_Date_col_str = "Ngày";
        //const string VS_Car_col_str = "Xe";
        //const string VS_Bill_col_str = "Card No";
        //const string VS_Bill_col_str_2 = "Mã chuẩn chi";
        //const string VS_Route_col_str = "Nội dung";
        //const string VS_OnFee_col_str = "Tiền Online";
        //const string VS_OffFee_col_str = "Tiền Offline";
        //const string VS_Other_Fee_col_str = "Phí khác";
        //const string VS_Other_Fee_col_str_2 = "Phí khác";
        //const string VS_CardNum_col_str = "Mã K.H";
        //const string VS_CardNum_col_str_2 = "Mã KH";

        const string VS_First_row_str = "Stt";
        const string VS_First_row_str_2 = "STT";
        const string VS_Date_col_str = "Ngày giao dịch";
        const string VS_Car_col_str = "Đội xe";
        const string VS_Bill_col_str = "Mã chuẩn chi";
        const string VS_Bill_col_str_2 = "Mã chuẩn chi";
        const string VS_Route_col_str = "Lộ trình";
        const string VS_OnFee_col_str = "Số tiền giao dịch";
        const string VS_OffFee_col_str = "Tiền Offline";
        const string VS_Other_Fee_col_str = "Chi khác";
        const string VS_Other_Fee_col_str_2 = "Phí khác";
        const string VS_CardNum_col_str = "Số thẻ Vinasun";
        const string VS_CardNum_col_str_2 = "Mã KH";
        

        const string ML_First_row_str = "card";
        const string ML_Date_col_str = "ngayp";
        const string ML_Car_col_str = "soxe";
        const string ML_Bill_col_str = "numb";
        const string ML_Route_col_str = "gchu";
        const string ML_Fee_col_str = "cuoc";
        const string ML_Other_Fee_col_str = "Phí khác";
        const string ML_CardNum_col_str = "card";


        DataTable VS_Card_dtb = new DataTable();
        DataSet VS_Card_ds = new DataSet();
        SqlDataAdapter VS_Card_da;

        DataTable ML_Card_dtb = new DataTable();
        DataSet ML_Card_ds = new DataSet();
        SqlDataAdapter ML_Card_da;

        private DateStringType DateTpye;
        
        #endregion

        private void Load_VSExcelfile(string filename)
        {
            int row, col;
            string cell_val;
            string strLine;
            bool ret = false;
            ADD_BILL_RESULT add_result;

            Excel.Worksheet xlSheet;
            if (File.Exists(filename) == false)
            {
                return;
            }

            Load_VSCardList();
            Load_MLCardList();
            DateTpye = DateStringType.DDMMYY;

            OpenWB = Open_excel_file(filename, "");
            xlSheet = (Excel.Worksheet)OpenWB.Worksheets.get_Item(1);

            // Find Cell need to check
            for (row = 1; row < 100; row++)
            {
                cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, 1, 1).Trim();
                if ((cell_val == VS_First_row_str)||(cell_val == VS_First_row_str_2))
                {
                    First_Row = row;
                    break;
                }
            }
            if (First_Row == 0)
            {
                MessageBox.Show("Can not find First_Row with value: Stt \nImport Fail", "Error");
                Close_WorkBook(OpenWB);
                return;
            }
            ProgressBar1.Visible = true;
            StatusLabel.Text = "Loadding File";

            for (col = 1; col < 100; col++)
            {
                cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, col, 1);
                if ((cell_val == VS_Bill_col_str)||(cell_val == VS_Bill_col_str_2)) Bill_col = col;
                if (cell_val == VS_Date_col_str) Date_col = col;
                if (cell_val == VS_Car_col_str) Car_col = col;
                if (cell_val == VS_Route_col_str) Route_col = col;
                if (cell_val == VS_OnFee_col_str) OnFee_Col = col;
                if (cell_val == VS_OffFee_col_str) OffFee_Col = col;
                if ((cell_val == VS_Other_Fee_col_str)||(cell_val == VS_Other_Fee_col_str_2))  Other_Fee_col = col;
                if ((cell_val == VS_CardNum_col_str)||(cell_val == VS_CardNum_col_str_2)) CardNum_col = col;
                
            }
            if (Bill_col == 0)
            {
                MessageBox.Show("Can not find 'Bill_col' Colum.\nImport Fail", "Error");
                ret = true;
            }
            if (Date_col == 0)
            {
                MessageBox.Show("Can not find 'Date' Colum.\nImport Fail", "Error");
                ret = true;
            }
            if (Car_col == 0)
            {
                MessageBox.Show("Can not find 'Car_col' Colum.\nImport Fail", "Error");
                ret = true;
            }
            if (Route_col == 0)
            {
                if (MessageBox.Show("Can not find 'Route_col' Colum.\nRoute always is assigned by empty", "Error", MessageBoxButtons.YesNo) == DialogResult.No)
                {
                    ret = true;
                }
            }

            // check for Fee col
            //if (OnFee_Col == 0)
            //{
            //    MessageBox.Show("Can not find 'Fee_col' Colum.\nImport Fail", "Error");
            //}
            //if (OffFee_Col == 0)
            //{
            //    MessageBox.Show("Can not find 'OnFee_col' Colum.\nImport Fail", "Error");
            //}
            if ((OnFee_Col == 0) && (OffFee_Col == 0))
            {
                MessageBox.Show("Can not find 'OnFee_Col & OffFee_Col' Colum.\nImport Fail", "Error");
                ret = true;
            }
            if (Other_Fee_col == 0)
            {
                MessageBox.Show("Can not find 'Others_Fee_col' Colum.\nImport Fail", "Error");
                ret = true;
            }

            if (CardNum_col == 0)
            {
                MessageBox.Show("Can not find 'CardNum_col' Colum.\nImport Fail", "Error");
                ret = true;
            }

            if (ret == true)
            {
                Close_WorkBook(OpenWB);
                // Thread.CurrentThread.Abort();
                return;
            }

            row++;
            cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, 1, 1);
            while (cell_val != "")
            {
                strLine = Get_VSExcelLine((Excel.Worksheet)OpenWB.Sheets[1], row);
                if (strLine == "")
                {
                    Review_BT_Click(null, null);
                    ((Excel.Range)((Excel.Worksheet)OpenWB.Sheets[1]).Cells[row, 1]).Interior.Color = 255;
                    break;
                }
                try
                {
                    add_result = AddDataRowToBillTable(strLine, Bill_List_dtb, "Bill_ID = ", 0, ';', "VinaSun");
                    if (add_result != ADD_BILL_RESULT.ADD_NEW)
                    {
                        ((Excel.Range)((Excel.Worksheet)OpenWB.Sheets[1]).Cells[row, 1]).Interior.Color = 255;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error At Line: " + row + ". With error message\n" + ex.ToString(), "Error");
                    ((Excel.Range)((Excel.Worksheet)OpenWB.Sheets[1]).Cells[row, 1]).Interior.Color = 255;
                    Review_BT_Click(null, null);
                    break;
                }
                ProgressBar1.Value = row % 100;
                row++;
                cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, 1, 1);
                cell_val = CheckStringOfNum(cell_val);
                if (cell_val == "")
                {
                    row++;
                    cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, 1, 1);
                    cell_val = CheckStringOfNum(cell_val);
                }
            }
            ProgressBar1.Visible = false;
            Close_WorkBook(OpenWB);
        }

        private void Load_MLExcelfile(string filename)
        {
            int row, col;
            string cell_val;
            string strLine;
            bool ret= false;
            ADD_BILL_RESULT add_result;

            Excel.Worksheet xlSheet;
            if (File.Exists(filename) == false)
            {
                return;
            }

            Load_VSCardList();
            Load_MLCardList();
            DateTpye = DateStringType.DDMMYY;

            OpenWB = Open_excel_file(filename, "");
            xlSheet = (Excel.Worksheet)OpenWB.Worksheets.get_Item(1);

            // Find Cell need to check
            for (row = 1; row < 100; row++)
            {
                cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, 1, 1).Trim();
                if (cell_val == ML_First_row_str)
                {
                    First_Row = row;
                    break;
                }
            }
            if (First_Row == 0)
            {
                MessageBox.Show("Can not find First_Row  \nImport Fail", "Error");
                // Application.Exit();
                Close_WorkBook(OpenWB);
                return;
            }
            ProgressBar1.Visible = true;
            StatusLabel.Text = "Loadding File";

            for (col = 1; col < 100; col++)
            {
                cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, col, 1);
                if (cell_val == ML_Bill_col_str) Bill_col = col;
                if (cell_val == ML_Date_col_str) Date_col = col;
                if (cell_val == ML_Car_col_str) Car_col = col;
                if (cell_val == ML_Route_col_str) Route_col = col;
                if (cell_val == ML_Fee_col_str) Fee_col = col;
                // if (cell_val == ML_Other_Fee_col_str) Other_Fee_col = col;
                if (cell_val == ML_CardNum_col_str) CardNum_col = col;

            }
            if (Bill_col == 0)
            {
                MessageBox.Show("Can not find 'Bill_col' Colum.\nImport Fail", "Error");
                ret = true;
            }
            if (Date_col == 0)
            {
                MessageBox.Show("Can not find 'Date' Colum.\nImport Fail", "Error");
                ret = true;
            }
            if (Car_col == 0)
            {
                MessageBox.Show("Can not find 'Car_col' Colum.\nImport Fail", "Error");
                ret = true;
            }
            if (Route_col == 0)
            {
                MessageBox.Show("Can not find 'Route_col' Colum.\nImport Fail", "Error");
                ret = true;
            }
            if (Fee_col == 0)
            {
                MessageBox.Show("Can not find 'Fee_col' Colum.\nImport Fail", "Error");
                ret = true;
            }
            if (CardNum_col == 0)
            {
                MessageBox.Show("Can not find 'CardNum_col' Colum.\nImport Fail", "Error");
                ret = true;
            }

            if (ret == true)
            {
                Close_WorkBook(OpenWB);
                // Thread.CurrentThread.Abort();
                return;
            }

            row++;
            cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, 1, 1);
            while (cell_val != "")
            {
                strLine = Get_MLExcelLine((Excel.Worksheet)OpenWB.Sheets[1], row);
                if (strLine == "")
                {
                    Review_BT_Click(null, null);
                    ((Excel.Range)((Excel.Worksheet)OpenWB.Sheets[1]).Cells[row, 1]).Interior.Color = 255;
                    break;
                }
                try
                {
                    add_result = AddDataRowToBillTable(strLine, Bill_List_dtb, "Bill_ID = ", 0, ';', "MaiLinh");
                    if (add_result != ADD_BILL_RESULT.ADD_NEW)
                    {
                        ((Excel.Range)((Excel.Worksheet)OpenWB.Sheets[1]).Cells[row, 1]).Interior.Color = 255;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error At Line: " + row + ". With error message\n" + ex.ToString(), "Error");
                    ((Excel.Range)((Excel.Worksheet)OpenWB.Sheets[1]).Cells[row, 1]).Interior.Color = 255;
                    Review_BT_Click(null, null);
                    break;
                }

                ProgressBar1.Value = row % 100;
                row++;
                cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, 1, 1);
                if (cell_val == "")
                {
                    row++;
                    cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, 1, 1);
                }
            }
            ProgressBar1.Visible = false;
            Close_WorkBook(OpenWB);
        }


        /// <summary>
        /// Name: Get_VSExcelLine
        /// Function: Get VS data line. 
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="row"></param>
        /// 
        /// <returns></returns>
        private string Get_VSExcelLine(Excel.Worksheet sheet, int row)
        {
            string ret_str = "", cel_str;
            string alias_name;
            string date_str;
            int on_fee = 0, off_fee = 0;
            Excel.Range cur_cell;
            string card_ID = ((Excel.Range)sheet.Cells[row, CardNum_col]).Text.ToString();       // card ID

            ((Excel.Range)sheet.Cells[row, CardNum_col]).Interior.Color = 13434879;

            try
            {
                //@NOTE: When Update new card name. able to use alias name feature for not create new card in list
                alias_name = Check_Alias_Name(card_ID);
                if (alias_name != "") card_ID = alias_name;

                if (Check_ItemExistTable(card_ID, VS_Card_dtb, "Card_Provider_Name") == false)
                {
                    MessageBox.Show("Card: " + card_ID + " not in List. \nImport Excel File Fail", "Error");
                    // inform in excel file
                    ((Excel.Range)sheet.Cells[row, CardNum_col]).Interior.Color = 255;
                    return "";
                }

                cel_str = ((Excel.Range)sheet.Cells[row, Bill_col]).Text.ToString().Trim();
                if (cel_str == "")
                {
                    MessageBox.Show("No BillID", "Error");
                    ((Excel.Range)sheet.Cells[row, Bill_col]).Interior.Color = 13434879;
                    return "";
                }
                ((Excel.Range)sheet.Cells[row, Bill_col]).Interior.Color = 13434879;
                ret_str = cel_str + ";";        // Bill ID
                ret_str += "VinaSun;;;";        // Taxi provide, username, department
                ((Excel.Range)sheet.Cells[row, Date_col]).NumberFormat = "[$-409]d-MMM-yy;@";
                cur_cell = (Excel.Range)sheet.Cells[row, Date_col];
                date_str = cur_cell.Text.ToString().Trim();
                ret_str += date_str + ";";      // Date
                ((Excel.Range)sheet.Cells[row, Date_col]).Interior.Color = 13434879;
                ret_str += card_ID + ";";       // card ID
                ret_str += ((Excel.Range)sheet.Cells[row, Car_col]).Text.ToString() + ";";           // taxi car ID
                ((Excel.Range)sheet.Cells[row, Car_col]).Interior.Color = 13434879;
                if (Route_col != 0)
                {
                    ret_str += ((Excel.Range)sheet.Cells[row, Route_col]).Text.ToString() + ";";     // Route
                    ((Excel.Range)sheet.Cells[row, Route_col]).Interior.Color = 13434879;
                }
                else
                {
                    ret_str += ";";
                }

                if (OnFee_Col != 0)
                {
                    ((Excel.Range)sheet.Cells[row, OnFee_Col]).NumberFormat = "0";
                    cel_str = ((Excel.Range)sheet.Cells[row, OnFee_Col]).Text.ToString().Trim();
                    if (cel_str != "")
                    {
                        on_fee = Convert.ToInt32(cel_str);
                    }
                    ((Excel.Range)sheet.Cells[row, OnFee_Col]).Interior.Color = 13434879;
                }
                if (OffFee_Col != 0)
                {
                    ((Excel.Range)sheet.Cells[row, OffFee_Col]).NumberFormat = "0";
                    cel_str = ((Excel.Range)sheet.Cells[row, OffFee_Col]).Text.ToString().Trim();
                    if (cel_str != "")
                    {
                        on_fee = Convert.ToInt32(cel_str);
                    }
                    ((Excel.Range)sheet.Cells[row, OffFee_Col]).Interior.Color = 13434879;
                }
                ret_str += (on_fee + off_fee).ToString() + ";";

                ret_str += ((Excel.Range)sheet.Cells[row, Other_Fee_col]).Text.ToString() + ";";     // other fee
                ((Excel.Range)sheet.Cells[row, Other_Fee_col]).Interior.Color = 13434879;
                ret_str += "N;;;;";             // Business, approve, lock, note
                ret_str += User_Info.UserName + ";";  // Change by
                ret_str += DateTime.Now.Date.ToString("dd/MM/yyyy") + ";";
                ret_str += "Excel Import";  // Change by
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show("Error when Get_VSExcelLine()", "Error");
                return "";
            }
            return ret_str.Trim();
        }

        private string Get_MLExcelLine(Excel.Worksheet sheet, int row)
        {
            string ret_str = "", cel_str;
            string alias_name;
            string card_ID = ((Excel.Range)sheet.Cells[row, CardNum_col]).Text.ToString();       // card ID

            ((Excel.Range)sheet.Cells[row, CardNum_col]).Interior.Color = 13434879;
            //@NOTE: When Update new card name. able to use alias name feature for not create new card in list
            alias_name = Check_Alias_Name(card_ID);
            if (alias_name != "") card_ID = alias_name;

            if (Check_ItemExistTable(card_ID, ML_Card_dtb, "Card_Provider_Name") == false)
            {
                MessageBox.Show("Card: " + card_ID + " not in List. \nImport Excel File Fail!", "Error");
                ((Excel.Range)sheet.Cells[row, CardNum_col]).Interior.Color = 255;
                //Close_WorkBook(OpenWB);
                return "";
            }


            cel_str = ((Excel.Range)sheet.Cells[row, Bill_col]).Text.ToString();
            if (cel_str == "")
            {
                MessageBox.Show("No BillID", "Error");
                ((Excel.Range)sheet.Cells[row, Bill_col]).Interior.Color = 13434879;
                return "";
            }
            ((Excel.Range)sheet.Cells[row, Bill_col]).Interior.Color = 13434879;
            ret_str = cel_str + ";";        // Bill ID
            ret_str += "MaiLinh;;;";          // Taxi provide, Useername , department
            ((Excel.Range)sheet.Cells[row, Date_col]).NumberFormat = "dd/mm/yy";
            ret_str += ((Excel.Range)sheet.Cells[row, Date_col]).Text.ToString() + ";";          // Date
            ((Excel.Range)sheet.Cells[row, Date_col]).Interior.Color = 13434879;
            ret_str += card_ID + ";";       // card ID
            ret_str += ((Excel.Range)sheet.Cells[row, Car_col]).Text.ToString() + ";";           // taxi car ID
            ((Excel.Range)sheet.Cells[row, Car_col]).Interior.Color = 13434879;
            ret_str += ((Excel.Range)sheet.Cells[row, Route_col]).Text.ToString() + ";";         // Route
            ((Excel.Range)sheet.Cells[row, Route_col]).Interior.Color = 13434879;
            ret_str += ((Excel.Range)sheet.Cells[row, Fee_col]).Text.ToString() + ";";           // Fee
            ((Excel.Range)sheet.Cells[row, Fee_col]).Interior.Color = 13434879;
            //ret_str += ((Excel.Range)sheet.Cells[row, Other_Fee_col]).Text.ToString() + ";";     // other fee
            ret_str += "0;";     // other fee
            ret_str += "N;;;;";             // Business, approve, lock, note
            ret_str += User_Info.UserName + ";";  // Change by
            ret_str += DateTime.Now.Date.ToString("dd/MM/yyyy") + ";";
            ret_str += "Excel Import";  // Change by
            return ret_str.Trim();
        }

        private string CheckStringOfNum(string str_check)
        {
            str_check = str_check.Trim();
            foreach (char sel_char in str_check)
            {
                if ((sel_char < '0') || (sel_char > '9'))
                {
                    return "";
                }
            }
            return str_check;
        }
    }
}